Real MySQL 8.0 (1)

ordilov / 2022. 3. 4

DBMS를 선택하는 기준 중 첫 번째는 "자기가 가장 잘 활용할 수 있는 DBMS"입니다. 그 다음으로는 안정성, 성능, 인지도가 있습니다.

비밀번호가 없는 관리자 계정 유저 생성하기

mysqld --defaults-file=/etc/my.cnf --initialize-insecure

시작과 종료

## 서버 시작
systemctl start mysqld

## 서버 상태 확인
systemctl status mysqld
ps -ef | grep mysqld

## 서버 종료
systemctl stop mysqld

## 원격 종료
mysql> SHUTDOWN;

클라이언트 없이 서버 연결 테스트

telnet 23.3.3.2 3306

## Netcat 으로 확인
nc 23.3.3.2 3306

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. /usr/etc/my.cnf
4. ~/my.cnf

다음 순서로 설정 파일을 찾으며 설정 파일이 다 있는 경우 어느 설정 파일을 참조했는지 찾을 수 있습니다.

mysqld --verbose --help

mysql --help 

SHOW GLOBAL VARIABLES;

글로벌 변수는 서버 인스턴스에서 전체적으로 영향을 미칩니다. 세션 변수는 각 클라이언트별로 유지되며 접속하면 기본값, 변경하면 세션별로 유지됩니다. 즉 커넥션 단위로 다른 값으로 변경이 가능한 값입니다. 예를 들면 autocommit 변수가 세션 변수입니다.

서버가 기동 중인 상태에서 변경 가능하냐에 따라 동적과 정적으로 구분됩니다. 변수는 설정 파일에서 기본적으로 불러오는데 기동 중에는 재시작 전에 변수가 변경되지 않습니다. 변수를 확인할 수 있는 방법은 명령어로 가능합니다.

SHOW GLOBAL VARIABLES LIKE '$max_connections$';

SET GLOBAL max_connections=500;

GLOBAL 변수의 경우 GLOBAL을 붙이면 되고 아닌 경우 자동으로 세션 변수입니다.

SET을 통해 GLOBAL 변수를 교체하더라도 서버를 재시작하면 변경되지 않습니다. 그 이유는 설정 파일에는 변경사항이 적용되지 않기 때문입니다. 적용하려면 PERSIST 명령어가 필요합니다. 반대로 삭제하려는 경우 RESET 명령어로 가능합니다.

SET PERSIST max_connections=5000;

RESET PERSIST max_connections;

MySQL에서는 사용자와 호스트명도 함께 계정의 일부가 됩니다. 사용자와 호스트명은 따옴표로 감싸서 표현합니다. 호스트의 경우 %를 통해 모든 IP나 모든 호스트명을 표현 가능합니다.

`svc_id`@`127.0.0.1` 

`svc_id`@`%`

사용자를 생성할 때 CREATE USER로 가능하며 여러 옵션이 있습니다.

IDENTIFIED WITH

비밀번호 인증 방식을 사용하는 경우 password를 함께 명시해야 합니다.

IDENTIFIED WITH `mysql_native_password` BY `password`

MYSQL 8.0 MySQL 8.0 버전부터 Caching SHA-2 Authentication이 기본 인증 방식입니다.
따라서 이 방식은 SSL 또는 RSA 키페어가 필요합니다. MYSQL 8.0 에서도 비밀번호를 기본 인증 방식으로 바꾸려면 설정 파일을 바꿔야 합니다.

SET GLOBAL default_authentication_plugin="mysql_native_password"

REQUIRE

SSL/TLS 채널을 사용할지 여부를 결정하는 옵션입니다.

응용 프로그램에서 데이터베이스 서버를 공용으로 사용하고 있는 경우가 많습니다. 이런 특성 떄문에 데이터베이스 서버의 계정 정보는 쉽게 변경하기 어렵고 서비스가 실행중일 때는 불가능합니다. 그래서 처음 설정한 비밀번호를 오래 사용하는 경우가 많습니다. 보안을 위해서 주기적으로 바꿔주려면 서비스를 모두 멈추지 않고서는 불가능한 일이었습니다. 이 같은 문제점을 해결하기 위해 8.0 버전 부터는 비림번호를 2개를 동시에 사용 가능합니다.

-- // old_password로 비밀번호 설정
ALTER USER `root`@`localhost` IDENTIFIED BY 'old_password';

-- // 비밀번호를 "qwerty"로 변경하면서 기존 비밀번호를 세컨더리로 변경
ALTER USER `root`@`localhost` IDENTIFIED BY `new_password` RETAIN CURRENT PASSWORD;

최근 설정된 비밀번호는 프라이머리로, 이전 비밀번호는 세컨더리 비밀번호가 됩니다. 변경한 이후에 사용중이던 서비스들에서 재시작마다 new_password로 변경하면 됩니다.
모든 서비스가 변경되고 나면 세컨더리 비밀번호에서 old_password를 삭제하면 됩니다.

ALTER USER `root`@`localhost` DISCARD OLD_PASSWORD;

MySQL 8.0에서 권한은 글로벌, 객체, 동적 권한으로 구분됩니다. 데이터베이스, 테이블 이외의 객체에 적용되는 권한은 글로벌 권한입니다. 객체 권한은 데이터베이스와 테이블에 해당하며 권한을 부여할 객체를 반드시 명시해야 합니다. ALL을 명시하는 경우 객체는 객체에 적용되는 모든 권한을, 글로벌은 글로벌 수준에서 모든 권한을 부여합니다. 미리 소스코드에 명시되어 있는 권한들은 정적 권한이라고 하며, 플러그인이나 추가로 등록되는 권한은 동적 권한입니다. 권하능ㄹ 부여할 때는 GRANT 명령어를 이용합니다.

GRANT privilege_list ON db.table TO 'user'@'host';

글로벌 권한의 경우 특정 대상에 부여될 수 없기 때문에 on 절에 항상 *.*을 이용합니다.

GRANT EVENT ON *.* TO 'user'@'localhost';

MySQL 8.0 부터는 권한들을 묶어서 역할로 사용할 수 있습니다.

CREATE ROLE
    role_emp_read,
    role_emp_write;

role_emp_read와 role_emp_write 역할을 생성했지만 아직 어떤 권한도 없는 껍데기입니다. GRANT 명ㄹ령을 통해 실질적인 권한을 부여할 수 있습니다.

GRANT SELECT ON employess.* TO role_emp_read;

역할을 계정에 부여하는 방법은 GRANT 명령어로 가능합니다.

GRANT role_emp_read TO reader@`127.0.0.1`;

역할에 부여되어 있는 권한은 SHOW GRANTS로 확인 가능합니다. 현재 계정의 역할을 확인은 SELECT current_role()로 가능합니다. 하지만 만든 상태에서 사용하려고하면 권한이 없다는 에러를 만나게 됩니다. 역할을 사용하려면 SET ROLE 명령어로 역할을 활성화해야 합니다. 로그아웃하고 다시 로그인하면 다시 활성화되지 않은 상태가 됩니다. 만약 로그인마다 역할을 자동으로 활성화하려면 옵션을 활성화해야 합니다.

SET activate_all_roles_on_login=ON;

mysql에서 user테이블을 살펴보면 역할과 사용자가 구분없이 저장되어 있습니다. 다만 역할들은 account_locked 칼럼의 값이 Y으로 되어 있습니다. 그리고 host는 %로 모든 호스트가 자동으로 추가됩니다. 따라서 역할을 부여하면 다른 계정과 권한을 합치는 것으로 처리됩니다.